{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "## 创建引擎"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "engine: Engine(mysql+pymysql://root:***@172.18.18.19:3306/stock)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm import declarative_base, Session\n",
    "from sqlalchemy import Column, Integer, String, create_engine\n",
    "import socket\n",
    "\n",
    "computer_name = socket.gethostname()\n",
    "if computer_name == 'fw':\n",
    "    engine = create_engine('mysql+pymysql://root:5fb5a420775da0b6@172.18.18.19:3306/stock')\n",
    "else:  # win=笔记本\n",
    "    engine = create_engine('mysql+pymysql://root:12345678@localhost:3306/stock')\n",
    "print(\"engine:\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 执行sql"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(198012, 107.5, 7.5, None, None, None, None, None, None, None, None, None, None)\n",
      "(198112, 102.5, 2.5, None, None, None, None, None, None, None, None, None, None)\n",
      "(198212, 102.0, 2.0, None, None, None, None, None, None, None, None, None, None)\n",
      "(198312, 102.0, 2.0, None, None, None, None, None, None, None, None, None, None)\n",
      "(198412, 102.7, 2.7, None, None, None, None, None, None, None, None, None, None)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.sql import text\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    result = connection.execute(text(\"SELECT * FROM cn_cpi limit 5\"))\n",
    "    for row in result:\n",
    "        print(row)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 创建表"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "Base = declarative_base()\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    age = Column(Integer)\n",
    "\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "with Session(engine) as session:\n",
    "    user = User(name='John Doe', age=30)\n",
    "    session.add(user)\n",
    "    session.commit()\n",
    "\n",
    "print(\"over\")\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## pandas读取数据库"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    month  nt_val  nt_yoy nt_mom nt_accu town_val town_yoy town_mom town_accu  \\\n",
      "0  198012   107.5     7.5   None    None     None     None     None      None   \n",
      "1  198112   102.5     2.5   None    None     None     None     None      None   \n",
      "2  198212   102.0     2.0   None    None     None     None     None      None   \n",
      "3  198312   102.0     2.0   None    None     None     None     None      None   \n",
      "4  198412   102.7     2.7   None    None     None     None     None      None   \n",
      "\n",
      "  cnt_val cnt_yoy cnt_mom cnt_accu  \n",
      "0    None    None    None     None  \n",
      "1    None    None    None     None  \n",
      "2    None    None    None     None  \n",
      "3    None    None    None     None  \n",
      "4    None    None    None     None  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from tools.dbTool import dbTool\n",
    "\n",
    "conn = dbTool.getDbConn()\n",
    "with conn:\n",
    "    res = pd.read_sql(text(\"select * from cn_cpi limit 5\"), conn)\n",
    "print(res)\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## sqlalchemy保存"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<tools.entity.MySignals object at 0x00000156C431A860>\n",
      "over\n"
     ]
    }
   ],
   "source": [
    "# 保存并更新, 不会返回保存对象\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from tools.dbTool import dbTool\n",
    "from sqlalchemy import sql\n",
    "from tools.entity import MySignals\n",
    "\n",
    "mysignal = MySignals(code='000001', stg='test', param='test', profit_rate=0.1, detail='test2')\n",
    "\n",
    "Session = sessionmaker(bind=dbTool.getEngine())\n",
    "with Session() as session:\n",
    "    session.merge(mysignal)\n",
    "    session.commit()\n",
    "# print(mysignal2)\n",
    "print(\"over\")"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}